package com.matt.foundation.utils;


import com.matt.commons.exception.BusinessException;
import com.matt.foundation.model.ErrorCode;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.BLANK;

/**
 *
 * @Description: poi工具类
 * @Company:
 * @create Author: Tim
 * @create Date: 2015-3-21 下午02:31:06
 * @version 1.0
 */
public class PoiUtill<T> {

    public  static  void main(String [] args) throws Exception{


        File pdfFile = new  File("D://leads导入模板.xls");
        FileInputStream fileInputStream = new FileInputStream(pdfFile);
        MultipartFile multipartFile = new MockMultipartFile(pdfFile.getName(), pdfFile.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);

     int ignoreRows =7 ;

        try {
         Map response  = readXls( multipartFile,  ignoreRows);
           boolean isCheck = (boolean)response.get("isCheck");
            List<String> dataList = (List<String>)response.get("list");
            if(isCheck){
                for(String dataStr : dataList){
                    System.out.println(dataStr);
                }
            }else{
                System.out.println("表格校验不通过，必填项不能为空");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 读写excel
     * @param file excel文件
     * @param cellNum excele列数
     * @return List里面包含List
     * @throws IOException
     */
    public static Map readXls(MultipartFile file, int cellNum) throws IOException {
        Map response = new HashMap<>();
        boolean isCheck = true ;
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = WorkbookFactory.create(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List list = new ArrayList();


        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

            Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                   continue;
                }
                List <String> dataList = new  ArrayList();
                String dataValue ="";
                // 循环列Cell
                for (int i=0;i<cellNum;i++){
                    Cell cell = hssfRow.getCell(i);
//                    if (cell == null) {
//                        isCheck = false ;
//                        String msg = "第"+rowNum+"行 第"+(i+1)+"列为空";
//                        System.out.println(msg);
//                        throw new BusinessException(ErrorCode.EXCEL_CHECK_ERROR, msg);
//                        break ;
//                        continue;
//                    }


                    String value ="";
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (date != null) {
                                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                    } else {
                                        value = "";
                                    }
                                } else {
                                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                                }
                                break;
                            case FORMULA:
                                // 导入时如果为公式生成的数据则无值
                                if (!cell.getStringCellValue().equals("")) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }
                                break;
                            case BLANK:
                                break;
                            case ERROR:
                                value = "";
                                break;
                            case BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                                break;
                            default:
                                value = "";
                        }

                    }

                    //System.out.println("第" + rowNum + "行 第" + (i + 1) + "列:" + value);
                    if(i== cellNum - 1){
                        dataValue += value ;
                    }else{
                        dataValue += value +",";
                    }

                 //   System.out.println("第" + rowNum + "行: " + dataValue);

                }
                list.add(dataValue);
             //   list.add(cellList);
            }
        }

        response.put("isCheck",isCheck);
        response.put("list",list);
        return response;
    }

    /**
     *
     * @param names 列名
     * @param list 内容list里面放list
     * @param excelName excel名字
     * @throws Exception
     */
    public void writeExcel(OutputStream out,String[] names,List<T> list,String excelName) throws Exception {
        // 创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet sheet = workbook.createSheet(excelName);

        sheet.setDefaultColumnWidth(15);

        // sheet 对应一个工作页
        HSSFRow row = sheet.createRow(0); // 下标为0的行开始
        for (int j = 0; j < names.length; j++) {
            HSSFCell cell = row.createCell(j);
            cell.setCellValue(new HSSFRichTextString(names[j]));
        }

        Iterator<T> it = list.iterator();

        int index = 0;

        while (it.hasNext()) {

            index++;

            row = sheet.createRow(index);

            T t = (T) it.next();

            //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

            Field[] fields = t.getClass().getDeclaredFields();

            for (int i = 0; i < fields.length; i++) {

                HSSFCell cell = row.createCell(i);

                Field field = fields[i];

                String fieldName = field.getName();

                String getMethodName = "get"

                        + fieldName.substring(0, 1).toUpperCase()

                        + fieldName.substring(1);

                try {

                    Class tCls = t.getClass();

                    Method getMethod = tCls.getMethod(getMethodName,

                            new Class[]{});

                    Object value = getMethod.invoke(t, new Object[]{});

                    //判断值的类型后进行强制类型转换

                    String textValue = value.toString();

                    HSSFRichTextString richString = new HSSFRichTextString(textValue);

                    HSSFFont font3 = workbook.createFont();
                    short  index1   = 0xc;
                    font3.setColor(index1);

                    richString.applyFont(font3);

                    cell.setCellValue(richString);



        /*for (int i = 0; i < list.size(); i++) {
            // 创建一行
            Row row = sheet.createRow(i + 1);
            // 得到要插入的每一条记录
            for (int colu = 0; colu <list.size(); colu++) {
                // 在一行内循环
                for (int cell=0;cell<names.length;cell++){
                    Cell xh = row.createCell(cell);
                    xh.setCellValue(list.get(i).get(cell)+"");
                }
            }
        }*/
                    // 创建文件输出流，准备输出电子表格
//        OutputStream out = new FileOutputStream("d:/eccsExcel/"+excelName+".xls");

                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        workbook.write(out);
    }
}
